########################################################################
#  Copyright (C) 2007,8,9 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert Citi N.A. (Citibank North America)
Portfolio (investment) Account Positions Detail CSV file to flat-OFX-RDF and
load into Sesame server.
"""
from csv2flt import *
import re
import os
import hashlib
from n3tagvalue import *

def timere(name):
    return '((?P<%s_hour>\d\d):(?P<%s_min>\d\d)(?P<%s_ampm>(am|pm)))'%(name,name,name)

htf_list=[
#version 0
([
['Description', 'Cusip#', 'Quantity', 'NAV/Price', 'Change', 'Price As of', 'Value', 'Account#']
],
[
    strre('SECNAME'),
    '#(?P<SECID>\d{9})000',
    floatre('UNITS'),
    pvaluere('UNITPRICE'),
    '(?P<CHANGE>[\-\+]\d+\.\d{2}|0)',
    '((?P<month>\d{1,2})/(?P<day>\d\d)/(?P<year>\d{2}))|((?P<hour>\d{1,2}):(?P<min>\d\d)(?P<ampm>(am|pm)))',
    pvaluere('MKTVAL',1),
    '(?P<ACCTID>[\d\-A-Z]+)'],
[
    ['Cash Account Balance', ' ', ' ', ' ', ' ', ' ', commanumre('UNITS'), ' '],
    ['Total\*', ' ', ' ', ' ', ' ', ' ', valuere('TOTAL',1), ' '],
    ['(?P<hour>\d{2}):(?P<min>\d{2}):(?P<sec>\d{2}) (?P<ampm>(AM|PM)) [A-Z][a-z]{2} (?P<day>\d{2}) (?P<month>[A-Z][a-z]*) (?P<year>\d{4}) ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']
    ]),
#version 1 from 6/2007
([
['Description \(SYM\)', 'Cusip#', 'Quantity', 'NAV/Price', 'Change', 'Price As of', 'Value', 'Account#']
],
[
    '(?P<SECNAME>.+)\((?P<TICKER>[A-Z]+)\)\Z',
    '#(?P<SECID>\d{9})000',
    floatre('UNITS'),
    pvaluere('UNITPRICE'),
    '(?P<CHANGE>[\-\+]\d+\.\d{2}|0)',
    '((?P<month>\d{1,2})/(?P<day>\d\d)/(?P<year>\d{2}))|((?P<hour>\d{1,2}):(?P<min>\d\d)(?P<ampm>(am|pm)))',
    pvaluere('MKTVAL',1),
    '(?P<ACCTID>[\d\-A-Z]+)'],
[
    ['Cash Account Balance', ' ', ' ', ' ', ' ', ' ', commanumre('UNITS'), ' '],
    ['Total\*', ' ', ' ', ' ', ' ', ' ', valuere('TOTAL',1), ' '],
    ['(?P<hour>\d{2}):(?P<min>\d{2}):(?P<sec>\d{2}) (?P<ampm>(AM|PM)) [A-Z][a-z]{2} (?P<day>\d{2}) (?P<month>[A-Z][a-z]*) (?P<year>\d{4}) ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']
    ]),
#version 2 from 9/2007
([
['Description', 'Cusip#', 'Quantity', 'NAV/Price', 'Change', 'Price As of', 'Value', 'Account#','Symbol']
],
[
    strre('SECNAME'),
    '#(?P<SECID>[A-Z\d]{9})000',
    floatre('UNITS'),
    pvaluere('UNITPRICE'),
    '(?P<CHANGE>[\-\+]\d+\.\d{2}|0)',
    '((?P<month>\d{1,2})/(?P<day>\d\d)/(?P<year>\d{2}))|((?P<hour>\d{1,2}):(?P<min>\d\d)(?P<ampm>(am|pm)))',
    pvaluere('MKTVAL',1),
    '(?P<ACCTID>[\d\-A-Z]+)',
    '(?P<TICKER>[A-Z]+)'],
[
    ['Cash Account Balance', ' ', ' ', ' ', ' ', ' ', commanumre('UNITS'), ' '],
    ['Total\*', ' ', ' ', ' ', ' ', ' ', valuere_optionalcomma('TOTAL',1), ' '],
    ['(?P<hour>\d{2}):(?P<min>\d{2}):(?P<sec>\d{2}) (?P<ampm>(AM|PM)) [A-Z][a-z]{2} (?P<day>\d{2}) (?P<month>[A-Z][a-z]*) 0?(?P<year>\d{4}) ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']
    ]),
]

def t24(hour,ampm):
    if ampm.upper()=='PM':
        return '%02d'%(int(hour)+12)
    else:
        return '%02d'%int(hour)

def flat(csvfile,fout,context=None,hashing=False):
    n3header(fout,"$Id$"[1:-1],context)
    h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)		

    # Extract from the footer the date in which the CSV file was extracted
    month={'January':'01','February':'02','March':'03','April':'04','May':'05',
           'June':'06','July':'07','August':'08','September':'09',
           'October':'10','November':'11','December':'12'}[f[2]['month']]
    statement_date=f[2]['year']+month+f[2]['day']
    statement_dtasof=(statement_date+t24(f[2]['hour'],f[2]['ampm'])+
                      f[2]['min']+f[2]['sec'])

    accttype='INVESTMENT'

    table_total=0
    accounts=[]
    for l in t:
        print >>fout,"a3:flatLine [ ",
        units=float(l['UNITS'].replace(',',''))
        unitprice=float(l['UNITPRICE'])
        mktval=float(l['MKTVAL'])
        if abs(mktval-units*unitprice)>0.5:
            print mktval,units*unitprice
        table_total+=mktval

        if l['year']:
            if len(l['year'])==4:
                dtasof=l['year']
            elif len(l['year'])==2:
                dtasof="20"+l['year']
            else:
                raise Exception("Bad year %s"%l['year'])
            if len(l['month'])==2:
                dtasof+=l['month']
            elif len(l['month'])==1:
                dtasof+="0"+l['month']
            else:
                raise Exception("Bad month %s"%l['month'])
            if len(l['day'])==2:
                dtasof+=l['day']
            elif len(l['day'])==1:
                dtasof+="0"+l['day']
            else:
                raise Exception("Bad day %s"%l['day'])
            dtasof+="235959" # last second of the day
        else:
            dtasof= statement_date+t24(l['hour'],l['ampm'])+l['min']+'59'
        print >>fout,stagvalue('dtasof',dtasof),
        secname=l['SECNAME']
        ticker=l.get('TICKER',None) # Only version>0 has TICKER

        print >>fout,n3secid('CUSIP',l['SECID']),
        print >>fout,stagvalue('SECNAME',secname),
        if ticker:
            print >>fout,stagvalue('TICKER',ticker),
        acctid=l['ACCTID']
        accounts.append(acctid)
        print >>fout,stagvalue('ACCTID',acctid,hashing=hashing),
        print >>fout,stagvalue('DTASOF',statement_dtasof),
        print >>fout,stagvalue('CURDEF','USD'),    
        print >>fout,stagvalue('ACCTTYPE','INVESTMENT'),
        print >>fout,stagvalue('UNITS',units),stagvalue('UNITPRICE',unitprice),
        print >>fout,stagvalue('MKTVAL',mktval),stagvalue('CURRENCY','USD'),
        print >>fout,"];"

    # Extract from the footer information on the cash account used for investment
    print >>fout,"a3:flatLine [ ",
    # surprisngly the information available is for one cash account even if
    # there are several investment accounts reported in the same CSV file
    # so we will generate an artifical account which cover cash in all accounts.
    accounts=[svalue('ACCTID',a,hashing=hashing)[0] for a in set(accounts)]
    accounts.sort()
    accounts="".join(accounts)        
    print >>fout,stagvalue('ACCTID',accounts),	# dont perform hashing
    print >>fout,stagvalue('DTASOF',statement_dtasof),
    cash = float(f[0]['UNITS'].replace(',',''))
    print >>fout,stagvalue('CURDEF','USD'),
    print >>fout,stagvalue('AVAILCASH',cash),
    print >>fout,stagvalue('MARGINBALANCE',0.),stagvalue('SHORTBALANCE',0.),
    print >>fout,"];"

    print >>fout,"."

    # Check total
    table_total+=cash
    total=float(f[1]['TOTAL'].replace(',',''))
    if abs(total-table_total)>0.5:
        print "Reported and computed totals dont match",total,table_total
